package com.xlsx.utils;

import org.apache.poi.hssf.usermodel.HSSFDataFormat;
import org.apache.poi.hssf.util.HSSFColor;
import org.apache.poi.ss.usermodel.*;
import org.apache.poi.ss.util.CellRangeAddressList;
import org.apache.poi.xssf.usermodel.*;

import java.math.BigDecimal;
import java.text.DecimalFormat;
import java.text.SimpleDateFormat;
import java.util.Date;
import java.util.HashMap;
import java.util.List;
import java.util.regex.Matcher;
import java.util.regex.Pattern;


public class ExcelUtils_Wang {
    /**
     * 表格头样式 12号宋体居中加粗
     * @param workbook
     * @return
     */
    public static XSSFCellStyle titleCellStyle(XSSFWorkbook workbook){
        // 单元格样式
        XSSFCellStyle cellStyle = workbook.createCellStyle();
        XSSFFont font = workbook.createFont();
        font.setFontName("宋体");
        font.setFontHeightInPoints((short)15);
        font.setColor(HSSFColor.BLACK.index);
        font.setBold(true); //粗体
        cellStyle.setFont(font);

        cellStyle.setBorderBottom(BorderStyle.THIN); // 下边框
        cellStyle.setBorderLeft(BorderStyle.THIN); // 左边框
        cellStyle.setBorderTop(BorderStyle.THIN); // 上边框
        cellStyle.setBorderRight(BorderStyle.THIN); // 右边框
        cellStyle.setVerticalAlignment(VerticalAlignment.CENTER); // 垂直居中
        cellStyle.setAlignment(HorizontalAlignment.CENTER); // 左右居中
        cellStyle.setWrapText(true); // 自动换行
        bgColor(cellStyle,236,245,255); // 设置背景色
        return cellStyle;
    }

    /**
     * 单元格样式
     */
    public static XSSFCellStyle bodyCellStyle(XSSFWorkbook workbook) {
        //单元格格式
        XSSFCellStyle cellStyle = workbook.createCellStyle();
        XSSFFont titleFont3 = workbook.createFont();
        titleFont3.setFontName("宋体");
        titleFont3.setFontHeightInPoints((short) 12);
        titleFont3.setColor(HSSFColor.BLACK.index);
        cellStyle.setFont(titleFont3);
        cellStyle.setBorderBottom(BorderStyle.THIN);// 下边框
        cellStyle.setBorderLeft(BorderStyle.THIN);// 左边框
        cellStyle.setBorderTop(BorderStyle.THIN);// 上边框
        cellStyle.setBorderRight(BorderStyle.THIN);// 右边框
        cellStyle.setVerticalAlignment(VerticalAlignment.CENTER);//垂直居中
        cellStyle.setAlignment(HorizontalAlignment.CENTER);//左右居中
        cellStyle.setWrapText(true);//自动换行
        return cellStyle;
    }

    /**
     * 自定义背景色
     */
    public static XSSFCellStyle bgColor(XSSFCellStyle cellStyle,int red,int green,int blue){
        // 颜色对象
        XSSFColor color = new XSSFColor();
        color.setRGB(intToByteArray(getIntFromColor(red,green,blue)));//自定义颜色
        cellStyle.setFillForegroundColor(color);
        cellStyle.setFillPattern(FillPatternType.SOLID_FOREGROUND); // 实心填充
        return cellStyle;
    }

    /**
     * 数值表格样式
     */
    public static XSSFCellStyle NumberCellStyle(XSSFWorkbook workbook){
        XSSFFont titleFont3 = workbook.createFont();
        titleFont3.setFontName("宋体");
        titleFont3.setFontHeightInPoints((short)12);
        titleFont3.setColor(HSSFColor.BLACK.index);
        //数值格式
        XSSFCellStyle numberCellStyle = workbook.createCellStyle();
        numberCellStyle.setFont(titleFont3);
        numberCellStyle.setDataFormat(HSSFDataFormat.getBuiltinFormat("#,##0.00"));
        numberCellStyle.setBorderBottom(BorderStyle.THIN);//下边框
        numberCellStyle.setBorderLeft(BorderStyle.THIN);//左边框
        numberCellStyle.setBorderTop(BorderStyle.THIN);//上边框
        numberCellStyle.setBorderRight(BorderStyle.THIN);//右边框
        numberCellStyle.setVerticalAlignment(VerticalAlignment.CENTER);// 垂直居中
        numberCellStyle.setAlignment(HorizontalAlignment.CENTER);//左右居中
        numberCellStyle.setWrapText(true); // 换行
        return numberCellStyle;
    }

    /**
     * 0.00% 表格样式
     */
    public static XSSFCellStyle PercentageCellStyle(XSSFWorkbook workbook){
        XSSFFont titleFont3 = workbook.createFont();
        titleFont3.setFontName("宋体");
        titleFont3.setFontHeightInPoints((short)12);
        titleFont3.setColor(HSSFColor.BLACK.index);
        //%格式
        XSSFCellStyle cellStyle2 = workbook.createCellStyle();
        cellStyle2.setFont(titleFont3);
        cellStyle2.setDataFormat(HSSFDataFormat.getBuiltinFormat("0.00%"));
        cellStyle2.setBorderBottom(BorderStyle.THIN);// 下边框
        cellStyle2.setBorderLeft(BorderStyle.THIN);//左边框
        cellStyle2.setBorderTop(BorderStyle.THIN);//上边框
        cellStyle2.setBorderRight(BorderStyle.THIN);//右边框
        cellStyle2.setVerticalAlignment(VerticalAlignment.CENTER);//垂直居中
        cellStyle2.setAlignment(HorizontalAlignment.CENTER);// 左右居中
        cellStyle2.setWrapText(true);
        return cellStyle2;
    }

    /**
     * 导出时转字符串
     */
    public static String getCellValue(Cell cell){
        String cellValue = "";
        if (cell == null){
            return cellValue;
        }
        // 判断数据类型
        switch (cell.getCellTypeEnum()){
            case NUMERIC: // 数字
                if (DateUtil.isCellDateFormatted(cell)){ // 如果是日期
                    Date date = cell.getDateCellValue();// 获得日期
                    cellValue = new SimpleDateFormat("yyyy/MM/dd").format(date);//格式化日期
                }else { // 如过不是日期，防止数字过长，转换格式
                    String s2 = new DecimalFormat("#.##########").format(cell.getNumericCellValue());//格式化数组
                    cellValue = new BigDecimal(s2).stripTrailingZeros().toPlainString();//去后面的0
                }
                break;
            case STRING: // 字符串
                cellValue = cell.getStringCellValue();
                break;
            case BOOLEAN: // Boolean
                cellValue = cell.getBooleanCellValue() + "";
                break;
            case FORMULA: // 公式
                cellValue = cell.getCellFormula() + "";
                break;
            case BLANK: // 空值
                cellValue = "";
                break;
            case ERROR: // 故障
                cellValue = "非法字符";
                break;
            default:
                cellValue = "未知类型";
                break;
        }
        return cellValue;
    }

    /**
     * 校验日期格式
     * @param datestr
     * @return
     * @throws Exception
     */
    public static boolean validDateEffecitive(String datestr) throws Exception {
        //日期校验
        String DATE_REGEX = "^([1-9]\\d{3}-)(([0]{0,1}[1-9]-)|([1][0-2]-))(([0-3]{0,1}[0-9]))$";
        boolean matches = Pattern.matches(DATE_REGEX, datestr);
        if (!matches) {
            return matches;
        }
        SimpleDateFormat sdf = new SimpleDateFormat("yyyy-MM-dd");
        //设置日期格式转的严谨性
        sdf.setLenient(false);
        sdf.parse(datestr);
        return matches;
    }

    //校验年
    public static boolean validYearEffecitive(String datestr)throws Exception{
        //日期校验
        String DATE_REGEX = "^([1-9]\\d{3})$";
        boolean matches = Pattern.matches(DATE_REGEX, datestr);
        if(!matches){
            return matches;
        }
        SimpleDateFormat sdf = new SimpleDateFormat("yyyy");
        //设置日期格式转的严谨性
        sdf.setLenient(false);
        return matches;
    }
    //校验项目分类是否存在
    public static String delPrjtClft(String prjtclft) {
        StringBuilder builder = new StringBuilder();
        for (int l = prjtclft.length() - 1; l >= 0; l--) {
            String word = prjtclft.substring(l, l + 1);
            if (word.matches("[a-zA-Z]+")) {
                builder.append(word.toUpperCase());
            } else if (word.matches("[0-9]+")) {
                builder.append(word);
            }
        }
        return builder.reverse().toString();
    }

    /**
     * 设置行-内容-样式
     * @param row 行本体
     * @param list 行内容
     * @param cellStyle 行样式
     */
    public static void setRow(XSSFRow row, List<String> list, XSSFCellStyle cellStyle){
        XSSFCell cell;
        for(int i=0;i<list.size();i++){
            cell = row.createCell(i);
            cell.setCellValue(list.get(i));
            cell.setCellStyle(cellStyle);
        }
    }
    public static void setRow(XSSFRow row, List<String> list){
        XSSFCell cell;
        for(int i=0;i<list.size();i++){
            cell = row.createCell(i);
            cell.setCellValue(list.get(i));
        }
    }
    /**
     * 设置下拉框
     * @param sheet 工作表对象
     * @param textList 下拉框内容
     * @param firstRow 开始行
     * @param endRow 结束行
     * @param firstCol 开始列
     * @param endCol 结束列
     */
    public static void setHSSFValidation(XSSFSheet sheet, String[] textList, int firstRow,int endRow,int firstCol,int endCol){
        DataValidationHelper helper = sheet.getDataValidationHelper();//设贵下拉框
        CellRangeAddressList addressList = new CellRangeAddressList(firstRow, endRow, firstCol,endCol);
        DataValidationConstraint constraint = helper.createExplicitListConstraint(textList);
        DataValidation dataValidation = helper.createValidation(constraint, addressList);
        if(dataValidation instanceof XSSFDataValidation){
            dataValidation.setSuppressDropDownArrow(true);
            dataValidation.setShowErrorBox(true);
        } else {
            dataValidation.setSuppressDropDownArrow(false);
        }
        sheet.addValidationData(dataValidation);
    }

    /**
     * cell内容校验
     * @param cell 单元格内容
     * @param rule 正则校验规则
     * @return
     */
    public static HashMap<String, Object> yaliCell(Cell cell,String rule){
        HashMap<String,Object> obj = new HashMap<>();
        obj.put("code",false);//状态码
        obj.put("cellName","");//处理完后的数据
        obj.put("message","");// 错误消息

        String cellName = getCellValue(cell).replaceAll(" ","");
        if (cellName != "" && cellName != null){
            boolean validate =validateCell(rule,cellName);
            if(validate){
                obj.put("code",true);// 状态码
                obj.put("cellName",cellName);//处理完后的数据
                return obj;
            }else {
                obj.put("message","不符合数据校验规则");// 错误消息
                return obj;
            }
        } else {
            obj.put("message","内容不能为空!");// 错误消息
            return obj;
        }
    }

    public static HashMap<String, Object> yaliCell(Cell cell,String rule,String ruleTip,int rowIndex,String head){
        HashMap<String,Object> obj = new HashMap<>();
        obj.put("code",false);//状态码
        obj.put("cellName","");//处理完后的数据
        obj.put("message","");// 错误消息

        String cellName = getCellValue(cell).replaceAll(" ","");
        if (cellName != "" && cellName != null){
            boolean validate =validateCell(rule,cellName);
            if(validate){
                obj.put("code",true);// 状态码
                obj.put("cellName",cellName);//处理完后的数据
                return obj;
            }else {
                obj.put("message","第"+(rowIndex+1)+"行的"+ruleTip);// 错误消息
                return obj;
            }
        } else {
            obj.put("message","第"+(rowIndex+1)+"行的"+head+"内容不能为空!");// 错误消息
            return obj;
        }
    }


    /**
     * 正则校验数据
     * @param rule
     * @param data
     * @return
     */
    public static boolean validateCell(String rule,String data){
        Pattern pattern = Pattern.compile(rule);
        Matcher matcher = pattern.matcher(data);
        return matcher.matches();
    }
    /**
     * rgb转int
     */
    private static int getIntFromColor(int Red, int Green, int Blue){
        Red = (Red << 16) & 0x00FF0000;
        Green = (Green << 8) & 0x0000FF00;
        Blue = Blue & 0x000000FF;
        return 0xFF000000 | Red | Green | Blue;
    }

    /**
     * int转byte[]
     */
    public static byte[] intToByteArray(int i) {
        byte[] result = new byte[4];
        result[0] = (byte)((i >> 24) & 0xFF);
        result[1] = (byte)((i >> 16) & 0xFF);
        result[2] = (byte)((i >> 8) & 0xFF);
        result[3] = (byte)(i & 0xFF);
        return result;
    }
}
